--    Copyright 2012 University of Southampton
-- 
--    Licensed under the Apache License, Version 2.0 (the "License");
--    you may not use this file except in compliance with the License.
--    You may obtain a copy of the License at
-- 
--        http://www.apache.org/licenses/LICENSE-2.0
-- 
--    Unless required by applicable law or agreed to in writing, software
--    distributed under the License is distributed on an "AS IS" BASIS,
--    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--    See the License for the specific language governing permissions and
--    limitations under the License.

USE [HDCMetadata]
GO
/****** Object:  Table [dbo].[Groups]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Groups](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](150) NOT NULL,
	[LeaderUserID] [int] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_Groups] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Users]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Username] [varchar](150) NOT NULL,
	[UserID] [varchar](184) NULL,
	[Name] [nvarchar](150) NULL,
	[Email] [varchar](150) NULL,
	[DefaultGroupID] [int] NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ExperimentCollections]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExperimentCollections](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Description] [nvarchar](150) NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_ExperimentCollections] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ExperimentDataFileTypes]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExperimentDataFileTypes](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](250) NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_ExperimentDataType] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ExperimentTypes]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExperimentTypes](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_ExperimentType] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Experiments]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Experiments](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[Owner] [int] NOT NULL,
	[FileSystemUserID] [nvarchar](184) NULL,
	[Date] [datetime] NOT NULL,
	[Description] [nvarchar](300) NULL,
	[ExperimentTypeID] [int] NOT NULL,
	[DefaultBasePath] [nvarchar](300) NOT NULL,
	[FileStoreOptionsExperimentID] [int] NULL,
	[IsDeleted] [bit] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_Experiments] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[UserGroupMap]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserGroupMap](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[GroupID] [int] NOT NULL,
	[UserID] [int] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_UserGroupMap] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[GroupAccess]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GroupAccess](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ExperimentID] [int] NOT NULL,
	[GroupID] [int] NOT NULL,
	[WriteAccessGranted] [bit] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_GroupAccess] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ExperimentLinks]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExperimentLinks](
	[ParentExperimentID] [int] NOT NULL,
	[LinkedExperimentID] [int] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_ExperimentLinks] PRIMARY KEY CLUSTERED 
(
	[ParentExperimentID] ASC,
	[LinkedExperimentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ExperimentTypeExperimentTemplateMap]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExperimentTypeExperimentTemplateMap](
	[ExperimentTypeID] [int] NOT NULL,
	[TemplateExperimentID] [int] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_ExperimentTypeExperimentTemplateMap] PRIMARY KEY CLUSTERED 
(
	[ExperimentTypeID] ASC,
	[TemplateExperimentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ExperimentDataFiles]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ExperimentDataFiles](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ExperimentID] [int] NOT NULL,
	[Filename] [nvarchar](1500) NOT NULL,
	[ExperimentDataFileTypeID] [int] NULL,
	[Description] [nvarchar](250) NULL,
	[BasePath] [nvarchar](300) NOT NULL,
	[LastWriteTime] [datetime] NOT NULL,
	[CreationTime] [datetime] NULL,
	[LastAccessTime] [datetime] NULL,
	[FileSize] [bigint] NULL,
	[SHA1Hash] [char](40) NULL,
	[SHA1HashGeneratedTime] [datetime] NULL,
	[Owner] [int] NOT NULL,
	[FileSystemUserID] [nvarchar](184) NULL,
	[FileStoreOptionsExperimentID] [int] NULL,
	[IsDeleted] [bit] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_ExperimentDataFiles] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ExperimentCollectionExperimentMap]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExperimentCollectionExperimentMap](
	[ExperimentCollectionID] [int] NOT NULL,
	[ExperimentID] [int] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_ExperimentCollectionExperimentMap] PRIMARY KEY CLUSTERED 
(
	[ExperimentCollectionID] ASC,
	[ExperimentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ExperimentParameters]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExperimentParameters](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ExperimentID] [int] NOT NULL,
	[Position] [int] NULL,
	[Name] [nvarchar](150) NOT NULL,
	[Value] [nvarchar](max) NULL,
	[Unit] [nvarchar](50) NULL,
	[Type] [nvarchar](50) NULL,
	[IsCompulsory] [bit] NULL,
	[RowVersion] [timestamp] NOT NULL,
	[SourceExperimentParameterID] [int] NULL,
 CONSTRAINT [PK_ExperimentParameters] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ExperimentParameterLinks]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExperimentParameterLinks](
	[ParentParameterID] [int] NOT NULL,
	[LinkedParameterID] [int] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_ExperimentParameterLinks] PRIMARY KEY CLUSTERED 
(
	[ParentParameterID] ASC,
	[LinkedParameterID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[UserAccess]    Script Date: 11/05/2012 19:25:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserAccess](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ExperimentID] [int] NOT NULL,
	[UserID] [int] NOT NULL,
	[WriteAccessGranted] [bit] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_UserAccess] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Default [DF_Experiments_IsDeleted]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[Experiments] ADD  CONSTRAINT [DF_Experiments_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO
/****** Object:  Default [DF_ExperimentDataFiles_IsDeleted]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentDataFiles] ADD  CONSTRAINT [DF_ExperimentDataFiles_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO
/****** Object:  Default [DF_ExperimentParameters_Type]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentParameters] ADD  CONSTRAINT [DF_ExperimentParameters_Type]  DEFAULT (N'unknown') FOR [Type]
GO
/****** Object:  Default [DF_ExperimentParameters_ParameterIsCompulsory]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentParameters] ADD  CONSTRAINT [DF_ExperimentParameters_ParameterIsCompulsory]  DEFAULT ((0)) FOR [IsCompulsory]
GO
/****** Object:  Default [DF_Experiments_ExperimentTypeID]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[Experiments] ADD  CONSTRAINT [DF_Experiments_ExperimentTypeID]  DEFAULT ((0)) FOR [ExperimentTypeID]
GO
/****** Object:  Default [DF_ExperimentParameters_ExperimentTemplateParameterParentID]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentParameters] ADD  CONSTRAINT [DF_ExperimentParameters_ExperimentTemplateParameterParentID]  DEFAULT ((0)) FOR [SourceExperimentParameterID]
GO
/****** Object:  Default [DF_Experiments_Owner]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[Experiments] ADD  CONSTRAINT [DF_Experiments_Owner]  DEFAULT ((0)) FOR [Owner]
GO
/****** Object:  Default [DF_GroupAccess_WriteAccessGranted]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[GroupAccess] ADD  CONSTRAINT [DF_GroupAccess_WriteAccessGranted]  DEFAULT ((0)) FOR [WriteAccessGranted]
GO
/****** Object:  Default [DF_UserAccess_WriteAccessGranted]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[UserAccess] ADD  CONSTRAINT [DF_UserAccess_WriteAccessGranted]  DEFAULT ((0)) FOR [WriteAccessGranted]
GO
/****** Object:  ForeignKey [FK_ExperimentCollectionExperimentMap_ExperimentCollections]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentCollectionExperimentMap]  WITH CHECK ADD  CONSTRAINT [FK_ExperimentCollectionExperimentMap_ExperimentCollections] FOREIGN KEY([ExperimentCollectionID])
REFERENCES [dbo].[ExperimentCollections] ([ID])
GO
ALTER TABLE [dbo].[ExperimentCollectionExperimentMap] CHECK CONSTRAINT [FK_ExperimentCollectionExperimentMap_ExperimentCollections]
GO
/****** Object:  ForeignKey [FK_ExperimentCollectionExperimentMap_Experiments]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentCollectionExperimentMap]  WITH CHECK ADD  CONSTRAINT [FK_ExperimentCollectionExperimentMap_Experiments] FOREIGN KEY([ExperimentID])
REFERENCES [dbo].[Experiments] ([ID])
GO
ALTER TABLE [dbo].[ExperimentCollectionExperimentMap] CHECK CONSTRAINT [FK_ExperimentCollectionExperimentMap_Experiments]
GO
/****** Object:  ForeignKey [FK_ExperimentDataFiles_ExperimentDatatype]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentDataFiles]  WITH CHECK ADD  CONSTRAINT [FK_ExperimentDataFiles_ExperimentDatatype] FOREIGN KEY([ExperimentDataFileTypeID])
REFERENCES [dbo].[ExperimentDataFileTypes] ([ID])
GO
ALTER TABLE [dbo].[ExperimentDataFiles] CHECK CONSTRAINT [FK_ExperimentDataFiles_ExperimentDatatype]
GO
/****** Object:  ForeignKey [FK_ExperimentDataFiles_Experiments]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentDataFiles]  WITH CHECK ADD  CONSTRAINT [FK_ExperimentDataFiles_Experiments] FOREIGN KEY([ExperimentID])
REFERENCES [dbo].[Experiments] ([ID])
GO
ALTER TABLE [dbo].[ExperimentDataFiles] CHECK CONSTRAINT [FK_ExperimentDataFiles_Experiments]
GO
/****** Object:  ForeignKey [FK_ExperimentDataFiles_Users]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentDataFiles]  WITH CHECK ADD  CONSTRAINT [FK_ExperimentDataFiles_Users] FOREIGN KEY([Owner])
REFERENCES [dbo].[Users] ([ID])
GO
ALTER TABLE [dbo].[ExperimentDataFiles] CHECK CONSTRAINT [FK_ExperimentDataFiles_Users]
GO
/****** Object:  ForeignKey [FK_ExperimentLinks_Experiments_Linked]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentLinks]  WITH CHECK ADD  CONSTRAINT [FK_ExperimentLinks_Experiments_Linked] FOREIGN KEY([LinkedExperimentID])
REFERENCES [dbo].[Experiments] ([ID])
GO
ALTER TABLE [dbo].[ExperimentLinks] CHECK CONSTRAINT [FK_ExperimentLinks_Experiments_Linked]
GO
/****** Object:  ForeignKey [FK_ExperimentLinks_Experiments_Parent]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentLinks]  WITH CHECK ADD  CONSTRAINT [FK_ExperimentLinks_Experiments_Parent] FOREIGN KEY([ParentExperimentID])
REFERENCES [dbo].[Experiments] ([ID])
GO
ALTER TABLE [dbo].[ExperimentLinks] CHECK CONSTRAINT [FK_ExperimentLinks_Experiments_Parent]
GO
/****** Object:  ForeignKey [FK_ExperimentParameterLinks_ExperimentParameters_Linked]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentParameterLinks]  WITH CHECK ADD  CONSTRAINT [FK_ExperimentParameterLinks_ExperimentParameters_Linked] FOREIGN KEY([LinkedParameterID])
REFERENCES [dbo].[ExperimentParameters] ([ID])
GO
ALTER TABLE [dbo].[ExperimentParameterLinks] CHECK CONSTRAINT [FK_ExperimentParameterLinks_ExperimentParameters_Linked]
GO
/****** Object:  ForeignKey [FK_ExperimentParameterLinks_ExperimentParameters_Parent]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentParameterLinks]  WITH CHECK ADD  CONSTRAINT [FK_ExperimentParameterLinks_ExperimentParameters_Parent] FOREIGN KEY([ParentParameterID])
REFERENCES [dbo].[ExperimentParameters] ([ID])
GO
ALTER TABLE [dbo].[ExperimentParameterLinks] CHECK CONSTRAINT [FK_ExperimentParameterLinks_ExperimentParameters_Parent]
GO
/****** Object:  ForeignKey [FK_ExperimentParameters_Experiments]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentParameters]  WITH CHECK ADD  CONSTRAINT [FK_ExperimentParameters_Experiments] FOREIGN KEY([ExperimentID])
REFERENCES [dbo].[Experiments] ([ID])
GO
ALTER TABLE [dbo].[ExperimentParameters] CHECK CONSTRAINT [FK_ExperimentParameters_Experiments]
GO
/****** Object:  ForeignKey [FK_Experiments_ExperimentType]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[Experiments]  WITH CHECK ADD  CONSTRAINT [FK_Experiments_ExperimentType] FOREIGN KEY([ExperimentTypeID])
REFERENCES [dbo].[ExperimentTypes] ([ID])
GO
ALTER TABLE [dbo].[Experiments] CHECK CONSTRAINT [FK_Experiments_ExperimentType]
GO
/****** Object:  ForeignKey [FK_Experiments_Users]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[Experiments]  WITH CHECK ADD  CONSTRAINT [FK_Experiments_Users] FOREIGN KEY([Owner])
REFERENCES [dbo].[Users] ([ID])
GO
ALTER TABLE [dbo].[Experiments] CHECK CONSTRAINT [FK_Experiments_Users]
GO
/****** Object:  ForeignKey [FK_ExperimentTypeExperimentTemplateMap_ExperimentTemplates]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentTypeExperimentTemplateMap]  WITH CHECK ADD  CONSTRAINT [FK_ExperimentTypeExperimentTemplateMap_ExperimentTemplates] FOREIGN KEY([TemplateExperimentID])
REFERENCES [dbo].[Experiments] ([ID])
GO
ALTER TABLE [dbo].[ExperimentTypeExperimentTemplateMap] CHECK CONSTRAINT [FK_ExperimentTypeExperimentTemplateMap_ExperimentTemplates]
GO
/****** Object:  ForeignKey [FK_ExperimentTypeExperimentTemplateMap_ExperimentTypes]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[ExperimentTypeExperimentTemplateMap]  WITH CHECK ADD  CONSTRAINT [FK_ExperimentTypeExperimentTemplateMap_ExperimentTypes] FOREIGN KEY([ExperimentTypeID])
REFERENCES [dbo].[ExperimentTypes] ([ID])
GO
ALTER TABLE [dbo].[ExperimentTypeExperimentTemplateMap] CHECK CONSTRAINT [FK_ExperimentTypeExperimentTemplateMap_ExperimentTypes]
GO
/****** Object:  ForeignKey [FK_GroupAccess_Experiments]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[GroupAccess]  WITH CHECK ADD  CONSTRAINT [FK_GroupAccess_Experiments] FOREIGN KEY([ExperimentID])
REFERENCES [dbo].[Experiments] ([ID])
GO
ALTER TABLE [dbo].[GroupAccess] CHECK CONSTRAINT [FK_GroupAccess_Experiments]
GO
/****** Object:  ForeignKey [FK_GroupAccess_Groups]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[GroupAccess]  WITH CHECK ADD  CONSTRAINT [FK_GroupAccess_Groups] FOREIGN KEY([GroupID])
REFERENCES [dbo].[Groups] ([ID])
GO
ALTER TABLE [dbo].[GroupAccess] CHECK CONSTRAINT [FK_GroupAccess_Groups]
GO
/****** Object:  ForeignKey [FK_Groups_Users]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[Groups]  WITH CHECK ADD  CONSTRAINT [FK_Groups_Users] FOREIGN KEY([LeaderUserID])
REFERENCES [dbo].[Users] ([ID])
GO
ALTER TABLE [dbo].[Groups] CHECK CONSTRAINT [FK_Groups_Users]
GO
/****** Object:  ForeignKey [FK_UserAccess_Experiments]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[UserAccess]  WITH CHECK ADD  CONSTRAINT [FK_UserAccess_Experiments] FOREIGN KEY([ExperimentID])
REFERENCES [dbo].[Experiments] ([ID])
GO
ALTER TABLE [dbo].[UserAccess] CHECK CONSTRAINT [FK_UserAccess_Experiments]
GO
/****** Object:  ForeignKey [FK_UserAccess_Users]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[UserAccess]  WITH CHECK ADD  CONSTRAINT [FK_UserAccess_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([ID])
GO
ALTER TABLE [dbo].[UserAccess] CHECK CONSTRAINT [FK_UserAccess_Users]
GO
/****** Object:  ForeignKey [FK_UserGroupMap_Groups]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[UserGroupMap]  WITH CHECK ADD  CONSTRAINT [FK_UserGroupMap_Groups] FOREIGN KEY([GroupID])
REFERENCES [dbo].[Groups] ([ID])
GO
ALTER TABLE [dbo].[UserGroupMap] CHECK CONSTRAINT [FK_UserGroupMap_Groups]
GO
/****** Object:  ForeignKey [FK_UserGroupMap_Users]    Script Date: 11/05/2012 19:25:51 ******/
ALTER TABLE [dbo].[UserGroupMap]  WITH CHECK ADD  CONSTRAINT [FK_UserGroupMap_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([ID])
GO
ALTER TABLE [dbo].[UserGroupMap] CHECK CONSTRAINT [FK_UserGroupMap_Users]
GO


-- =============================================
-- Author:		Mark Scott
-- Create date: 13/05/2013
-- Description:	Looks up if a user has permission to an experiment and returns 0 for no access, 1 for read access and 2 for write access
-- =============================================
CREATE FUNCTION [dbo].[ufnGetUserAccessLevel] 
(
	-- Add the parameters for the function here
	@UserID int,
    @ExperimentID int
)
RETURNS int
AS
BEGIN

	DECLARE @UserSID varchar(150)
	DECLARE @IsAdminOrOwner bit
	DECLARE @AccessLevel int

	-- Look up the user's SID
	SET @UserSID=(SELECT UserID FROM Users WHERE ID=@UserID)

    -- Check we found the user (if not, deny access)
    IF @UserSID IS NULL
    BEGIN
		RETURN 0
	END

	-- Check if they are the owner (or an administrator)
	SET @IsAdminOrOwner=
	(
	  CASE
	  WHEN
		--Check if the user is an admin:
		   EXISTS(SELECT NULL FROM UserGroupMap WHERE GroupID=0 AND UserID=@UserID)
		--Or if they are the owner:
		OR EXISTS(SELECT NULL FROM Experiments WHERE ID=@ExperimentID AND ( FileSystemUserID=@UserSID OR Owner=@UserID) )
		THEN 1
	  ELSE 0
	  END
	)

	-- Check if they have access
	SET @AccessLevel=
	(
		CASE
		WHEN @IsAdminOrOwner=1 THEN 2
		ELSE
			-- Check if they have been granted access to the experiment explicitly:
			ISNULL((SELECT WriteAccessGranted+1 FROM UserAccess WHERE ExperimentID=@ExperimentID AND UserID=@UserID),0)
			-- Check if they are the owner of a group that has been granted permission:
		  + ISNULL((SELECT gpacc.WriteAccessGranted+1 FROM GroupAccess AS gpacc INNER JOIN Groups AS grp ON gpacc.GroupID=grp.ID
					WHERE gpacc.ExperimentID=@ExperimentID AND grp.LeaderUserID=@UserID),0)
			-- Check if they are in a group that has been granted permission:
		  + ISNULL((SELECT gpacc.WriteAccessGranted FROM GroupAccess AS gpacc INNER JOIN UserGroupMap AS ugmap ON gpacc.GroupID=ugmap.GroupID
					WHERE gpacc.ExperimentID=@ExperimentID AND ugmap.UserID=@UserID),0)
		END
	)

	-- Return the result of the function
	RETURN @AccessLevel

END

GO

-- =============================================
-- Author:		Mark Scott
-- Create date: 13/05/2013
-- Description:	Returns experiments a user has permission to
-- =============================================
CREATE PROCEDURE [dbo].[spGetUsersExperiments] 
	@UserID int,
	@AccessLevel int = 1
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT * FROM Experiments as ex WHERE dbo.ufnGetUserAccessLevel(@UserID, ex.ID) > @AccessLevel;
END

GO


